package com.cz.fra.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cz.fra.dao.CommentDao;
import com.cz.fra.dao.EntityNotFoundException;
import com.cz.fra.dao.PersistenceException;
import com.cz.fra.entity.Comment;
import com.cz.fra.utils.DBUtil;

public class JdbcCommentDao implements CommentDao {

	private DBUtil dbUtil = new DBUtil();
	
	@Override
	public String addComment(Comment comment) throws EntityNotFoundException,
			PersistenceException {
		String add_comment_sql = "insert into "+ DBUtil.databaseName +".frs_comment (report_id, content, comment_date, comment_by) values ('"+comment.getReportId()+"','"+comment.getContent()+"',CURRENT_TIMESTAMP,'"+comment.getCommentBy()+"')";
		dbUtil.getConnection();
		if(dbUtil.executeUpdate(add_comment_sql)){
			return "Success";
		}else{
			return "Failed";
		}
	}

	@Override
	public List<Comment> getCommentsByReportID(int reportId)
			throws EntityNotFoundException, PersistenceException {
		String get_comments_by_report_id = "select * from "+ DBUtil.databaseName +".frs_comment where report_id = '"+reportId+"'" ;
		ResultSet rs = null;
		Comment comment = null;
		List<Comment> commentList = new ArrayList<Comment>();
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_comments_by_report_id);
			while(rs.next()){
				comment = mapRow(rs);
				commentList.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return commentList;
	}

	@Override
	public int getCommentNoByReportID(int reportId)
			throws EntityNotFoundException, PersistenceException {
		String get_comment_number_by_report_id = "select count(*) as comment_count from "+ DBUtil.databaseName +".frs_comment where report_id = '"+reportId+"'";
		ResultSet rs = null;
		int comment_count = 0;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_comment_number_by_report_id);
			if(rs.next()){
				comment_count = rs.getInt("comment_count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return comment_count;
	}

	public Comment mapRow(ResultSet rs) throws SQLException{
		Comment comment = new Comment();
		
		comment.setCommentId(rs.getInt("comment_id"));
		comment.setContent(rs.getString("content"));
		comment.setReportId(rs.getInt("report_id"));
		comment.setCommentBy(rs.getInt("comment_by"));
		comment.setCommentDate(rs.getTimestamp("comment_date"));
		
		return comment;
	}
}
